﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class BillingOrderDA : IBillingOrderDA
    {
        private string SQL_SELECT_ALL_BILLING_ORDER = " SELECT ORDERS.DOC_TYPE AS DOC_TYPE,TYPE_DESCR,WH_ID,ORDERS.DOC_ID AS DOC_ID,EXTERNAL_DOC_ID,OWNER_ID,CONVERT(VARCHAR(20),CREATED_DATE,23) AS CREATED_DATE,  CREATED_BY,REMARK,STATUS,STATUS_DESCR ,CASE INV_PALLET.CASED WHEN 'Y' THEN 'Y' ELSE 'N' END AS CASED,IS_PALLET_AUDIT AS IS_PALLET_AUDIT FROM (   SELECT 'InBound' AS DOC_TYPE,'入库' AS TYPE_DESCR,R.WH_ID,R.RECEIPT_ID AS DOC_ID,R.EXTERNAL_RECEIPT_ID AS EXTERNAL_DOC_ID,R.OWNER_ID,  R.CREATED_DATE,R.CREATED_BY,R.REMARK,R.STATUS,T.[TEXT] AS STATUS_DESCR,R.IS_PALLET_AUDIT AS IS_PALLET_AUDIT  FROM @_@RECEIPT R   LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+R.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE   INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE R.STATUS IN ('InReceiving','ReceivedComplete')   UNION   SELECT  'OutBound' AS DOC_TYPE,'出库' AS TYPE_DESCR,O.WH_ID,O.ORDER_ID AS DOC_ID,O.EXTERN_ORDER_ID AS EXTERNAL_DOC_ID,O.OWNER_ID,   O.CREATED_DATE,O.CREATED_BY,O.REMARK,O.STATUS,T.[TEXT] AS STATUS_DESCR,O.IS_PALLET_AUDIT AS IS_PALLET_AUDIT   FROM @_@ORDERS O   LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE   LEFT JOIN (SELECT TOP 1 OD.ORDER_ID FROM @_@ORDER_DETAIL OD WHERE   (OD.STATUS = 'Shipped' OR OD.STATUS = 'Close' OR (OD.STATUS = 'PartShipped'  AND OD.PREALLOCATED_QTY = 0 AND OD.ALLOCATED_QTY = 0 AND OD.PICKED_QTY = 0))) A ON A.ORDER_ID = O.ORDER_ID   INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE O.STATUS IN ('PartShipped','Shipped')   AND O.ORDER_ID NOT IN (SELECT DISTINCT OD.ORDER_ID FROM @_@ORDER_DETAIL OD WHERE (OD.PREALLOCATED_QTY <> 0 OR OD.ALLOCATED_QTY <> 0 OR OD.PICKED_QTY <> 0))   UNION   SELECT 'Movement' AS DOC_TYPE,'移动' AS TYPE_DESCR,M.WH_ID,M.MOVEMENT_ID AS DOC_ID,'' AS EXTERNAL_DOC_ID,M.OWNER_ID,   M.CREATED_DATE,M.CREATED_BY,M.REMARK,M.STATUS,T.[TEXT] AS STATUS_DESCR ,'' AS IS_PALLET_AUDIT  FROM @_@MOVEMENT M   LEFT JOIN sysadm.TRANSLATION T ON 'MovementStatus.'+M.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE   INNER JOIN SYSADM.USER_OWNER UO ON M.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE STATUS IN ('Complete')   ) ORDERS   LEFT JOIN ( SELECT DISTINCT DOC_ID AS DOC_ID2, CASE DOC_TYPE WHEN 'ASN' THEN 'INBOUND' WHEN 'SO' THEN 'OUTBOUND' ELSE '' END AS DOC_TYPE2,'Y' AS CASED FROM @_@INV_PALLET ) INV_PALLET ON ORDERS.DOC_ID = INV_PALLET.DOC_ID2 AND ORDERS.DOC_TYPE = INV_PALLET.DOC_TYPE2 ";

        public DataSet GetAllBillingOrder(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllBillingOrder(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public BillingOrderInfo GetBillingOrderByOrderIDAndOrderType(string orderID, string orderType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID) + " WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", orderID), new SqlParameter("@DOC_TYPE", orderType), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            BillingOrderInfo billingOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    billingOrderInfo = new BillingOrderInfo(reader["WH_ID"].ToString(), reader["DOC_ID"].ToString(), reader["EXTERNAL_DOC_ID"].ToString(), reader["DOC_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["STATUS"].ToString(), reader["STATUS_DESCR"].ToString(), reader["REMARK"].ToString(), reader["CREATED_DATE"].ToString(), reader["CREATED_BY"].ToString());
                }
            }
            return billingOrderInfo;
        }

        public BillingOrderInfo GetBillingOrderByOrderIDAndOrderType(DataBase dataBase, DbTransaction tran, string orderID, string orderType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID) + " WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", orderID), new SqlParameter("@DOC_TYPE", orderType), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            BillingOrderInfo billingOrderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                billingOrderInfo = new BillingOrderInfo(reader["WH_ID"].ToString(), reader["DOC_ID"].ToString(), reader["EXTERNAL_DOC_ID"].ToString(), reader["DOC_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["STATUS"].ToString(), reader["STATUS_DESCR"].ToString(), reader["REMARK"].ToString(), reader["CREATED_DATE"].ToString(), reader["CREATED_BY"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return billingOrderInfo;
        }

        public DataSet GetBillingOrderByQueryList(List<string> sqlWhere, BillingOrderQueryEntity billingOrderQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!billingOrderQueryEntity.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, billingOrderQueryEntity.CurrentPage, billingOrderQueryEntity.PageSize, billingOrderQueryEntity.SortField, billingOrderQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetBillingOrderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, BillingOrderQueryEntity billingOrderQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_BILLING_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, billingOrderQueryEntity.CurrentPage, billingOrderQueryEntity.PageSize, billingOrderQueryEntity.SortField, billingOrderQueryEntity.SortDirection).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }
    }
}

